using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Xml;
using System.Web;

namespace sdf.Code
{
	/// <summary>
	/// Helper functions for standard database activities.
	/// </summary>
	public class DbUtil
	{
		//todo: fetch alternate connection strings name
		public readonly static string CONNECTION_STRING = getConnectionString("ConnectionString1", true);

		/// *********************************************************************************
		#region Connections And Commands
		/// *********************************************************************************

		/// <summary>
		/// Looks up connection string via ConfigurationManager and optionally throws an error if not found.
		/// </summary>
		/// <param name="sStringName">Connection string name (as defined in webconfig)</param>
		/// <param name="bThrowErrorIfNotFound"></param>
		/// <returns></returns>																			 
		private static string getConnectionString(string sStringName, bool bThrowErrorIfNotFound)
		{
			string sConnectionString = "";

			try
			{
				sConnectionString = ConfigurationManager.ConnectionStrings[sStringName].ConnectionString;
			}
			catch (Exception ex)
			{
				//throw error, preserving inner. else fall through and return empty string
				if (bThrowErrorIfNotFound)
				{
					throw (new Exception("Connection string not found: " + sStringName, ex));
				}
			}

			return sConnectionString;
		}

		/// -------------------------------------------------------------------
		/// <summary>
		///	Opens SqlConnection and returns it 
		/// </summary>
		/// -------------------------------------------------------------------
		public static System.Data.SqlClient.SqlConnection GetConnection()
		{
			SqlConnection sqlConnection = new SqlConnection(CONNECTION_STRING);
			sqlConnection.Open();

			return sqlConnection;
		}

		/// -------------------------------------------------------------------
		/// <summary>
		///	Fetch and return command object
		/// </summary>
		/// -------------------------------------------------------------------
		public static System.Data.SqlClient.SqlCommand GetCommand(System.Data.SqlClient.SqlConnection cn, System.Data.CommandType CmdType, string sSQL)
		{
			System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sSQL, cn);
			cmd.CommandType = CmdType;

			return cmd;
		}

		/// *********************************************************************************
		#endregion Connections And Commands
		/// *********************************************************************************

		/// *********************************************************************************
		#region Readers And Adapters
		/// *********************************************************************************

		/// -------------------------------------------------------------------
		/// <summary>
		///	Fills and returns an XMLDocument. Assumes provided CMD object has 
		///	already been populated. Optionally throws error if SQL Return Value is non-zero.
		/// </summary>
		/// -------------------------------------------------------------------
		public static System.Xml.XmlDocument FillXMLDocumentAndClose(SqlCommand cmd, bool bCheckSqlReturnValue)
		{
			//Execute proc and fill xml reader
			System.Xml.XmlReader myXMLReader = cmd.ExecuteXmlReader();
			System.Xml.XmlDocument myXMLDocument = new System.Xml.XmlDocument();
			myXMLDocument.Load(myXMLReader);

			//check return value for error
			CheckReturnValue(cmd, bCheckSqlReturnValue);

			return myXMLDocument;
		}

		/// -------------------------------------------------------------------
		/// <summary>
		///	Fills and returns a XMLReader. Assumes provided CMD object has 
		///	already been populated. Optionally throws error if SQL Return Value is non-zero.
		/// </summary>
		/// -------------------------------------------------------------------
		public static System.Xml.XmlReader FillXMLReaderAndClose(SqlCommand cmd, bool bCheckSqlReturnValue)
		{
			//Execute proc and fill xml reader
			System.Xml.XmlReader myXMLReader = cmd.ExecuteXmlReader();
			myXMLReader.Close();

			//check return value for error
			CheckReturnValue(cmd, bCheckSqlReturnValue);

			return myXMLReader;
		}

		/// -------------------------------------------------------------------
		/// <summary>
		///	Fills provided a datatable. Assumes provided CMD object has 
		///	already been populated. Optionally throws error if SQL Return Value is non-zero.
		/// </summary>
		/// -------------------------------------------------------------------
		public static DataTable FillDataTable(SqlCommand cmd, bool bCheckSqlReturnValue, out string sReturnMessage)
		{
			// sing supplied command object, create and fill dataset
			System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();
			da.SelectCommand = cmd;

			//fill datatable
			DataTable dt = new DataTable();
			da.Fill(dt);

			//check return value for error
			CheckReturnValue(cmd, bCheckSqlReturnValue);

			//get return message from proc
			sReturnMessage = GetProcReturnMessage(cmd);

			//return table
			return dt;
		}


		//overload -- does not return return message
		public static System.Data.DataTable FillDataTable(SqlCommand cmd, bool bCheckSqlReturnValue)
		{
			string sDummyMessage;

			//return dataset
			return FillDataTable(cmd, bCheckSqlReturnValue, out sDummyMessage);
		}

		//overload, returns SQL Return Message

		// *********************************************************************************
		#endregion Readers And Adapters
		// *********************************************************************************

		// *********************************************************************************
		#region Parameters
		// ********************************************************************************
	
		/// <summary>
		/// Adds ADO Return parameter to command object without specifying size.
		/// </summary>
		/// <param name="Cmd"></param>
		/// <param name="pSqlType"></param>
		/// <param name="pParamName"></param>
		public static void AddParamReturnValue(SqlCommand Cmd, SqlDbType pSqlType, string pParamName)
		{
			//add the parameter
			Cmd.Parameters.Add(pParamName, pSqlType);
			Cmd.Parameters[pParamName].Direction = ParameterDirection.ReturnValue;
		}

		/// <summary>
		/// Adds ADO Return parameter to command object with size specified
		/// </summary>
		/// <param name="Cmd"></param>
		/// <param name="pSqlType"></param>
		/// <param name="pParamName"></param>
		/// <param name="pLength">Length of return (if string)</param>
		public static void AddParamReturnValue(SqlCommand Cmd, SqlDbType pSqlType, string pParamName, int pLength)
		{
			//add the parameter
			Cmd.Parameters.Add(pParamName, pSqlType);
			Cmd.Parameters[pParamName].Direction = ParameterDirection.ReturnValue;
			Cmd.Parameters[pParamName].Size = pLength;

			//verify that the parameter type received is compatible with the SqlDbType
			if (!ParamIsLengthType(pSqlType))
			{
				throw new ArgumentOutOfRangeException(pParamName, pSqlType.ToString() + " is not a length type");
			}
		}

		/// <summary>
		/// Adds ADO Output parameter to command object without length.
		/// </summary>
		/// <param name="Cmd"></param>
		/// <param name="pSqlType"></param>
		/// <param name="pParamName"></param>
		public static void AddParamOutput(SqlCommand Cmd, SqlDbType pSqlType, string pParamName)
		{
			//add the parameter
			Cmd.Parameters.Add(pParamName, pSqlType);
			Cmd.Parameters[pParamName].Direction = ParameterDirection.Output;
		}

		/// <summary>
		/// Adds ADO Output parameter to command object with length.
		/// </summary>
		/// <param name="Cmd"></param>
		/// <param name="pSqlType"></param>
		/// <param name="pParamName"></param>
		/// <param name="pLength">Length of return (if string)</param>
		public static void AddParamOutput(SqlCommand Cmd, SqlDbType pSqlType, string pParamName, int pLength)
		{
			//add the parameter
			Cmd.Parameters.Add(pParamName, pSqlType);
			Cmd.Parameters[pParamName].Direction = ParameterDirection.Output;
			Cmd.Parameters[pParamName].Size = pLength;

			//verify that the parameter type received is compatible with the SqlDbType
			if (!ParamIsLengthType(pSqlType))
			{
				throw new ArgumentOutOfRangeException(pParamName, pSqlType.ToString() + " is not a length type");
			}
		}

		/// <summary>
		/// Adds ADO Input parameter to command object. Accepts generic parameter value type.
		/// Use this overload when length should not be checked.
		/// </summary>
		/// <typeparam name="T">Type of pvalue param</typeparam>
		/// <param name="Cmd"></param>
		/// <param name="pSqlType"></param>
		/// <param name="pParamName"></param>
		/// <param name="pValue"></param>
		public static void AddParamInput<T>(SqlCommand Cmd, SqlDbType pSqlType, string pParamName, T pValue)
		{
			ValidateParamType<T>(pSqlType, pParamName, pValue);

			//add the parameter
			Cmd.Parameters.Add(pParamName, pSqlType);
			Cmd.Parameters[pParamName].Direction = ParameterDirection.Input;
			Cmd.Parameters[pParamName].Value = pValue;

			//convert null to dbnull
			if (Cmd.Parameters[pParamName].Value == null)
			{
				Cmd.Parameters[pParamName].Value = DBNull.Value;
			}
		}

		/// <summary>
		/// Adds ADO parameter to command object. Accepts generic value type.
		/// Use this overload to check the length of the value (string types only).
		/// </summary>
		/// <typeparam name="T">Type of pvalue param</typeparam>
		/// <param name="Cmd"></param>
		/// <param name="pSqlType"></param>
		/// <param name="pParamName"></param>
		/// <param name="pValue"></param>
		/// <param name="pLength"></param>
		public static void AddParamInput<T>(SqlCommand Cmd, SqlDbType pSqlType, string pParamName, T pValue, int pLength)
		{
			//validate type
			ValidateParamType<T>(pSqlType, pParamName, pValue);

			//verify that the parameter type received is compatible with the SqlDbType
			if (ParamIsLengthType(pSqlType))
			{
				if (!(pValue == null))
				{
					//verify it is of string type
					if (!(pValue is String))
					{
						RaiseParamTypeException(pParamName, pValue.GetType().Name, "String");
					}

					//verify length
					if (pValue.ToString().Length > pLength)
					{
						throw new ArgumentOutOfRangeException("Parameter '" + pParamName + "' exceeds max length of " + pLength.ToString());
					}
				}
			}
			else
			{
				throw new ArgumentOutOfRangeException(pSqlType.ToString() + " is not a recognized type");
			}

			//add the parameter
			Cmd.Parameters.Add(pParamName, pSqlType);
			Cmd.Parameters[pParamName].Direction = ParameterDirection.Input;
			Cmd.Parameters[pParamName].Value = pValue;
			Cmd.Parameters[pParamName].Size = pLength;

			//convert null to dbnull
			if (Cmd.Parameters[pParamName].Value == null)
			{
				Cmd.Parameters[pParamName].Value = DBNull.Value;
			}
		}
	
		/// <summary>
		/// Call by AddParam functions to verify that the provided SqlType is compatible with the provided value.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="pSqlType"></param>
		/// <param name="pParamName"></param>
		/// <param name="pValue"></param>
		private static void ValidateParamType<T>(SqlDbType pSqlType, string pParamName, T pValue)
		{
			if (pValue == null)
			{
				return;
			}

			//verify that the parameter type received is compatible with the SqlDbType
			switch (pSqlType)
			{
				case SqlDbType.DateTimeOffset:
				case SqlDbType.Date:
				case SqlDbType.SmallDateTime:
				case SqlDbType.DateTime:
				case SqlDbType.DateTime2:
					{
						if (!(pValue is DateTime))
						{
							RaiseParamTypeException(pParamName, pValue.GetType().Name, "DateTime");
						}
						break;
					}
				case SqlDbType.Bit:
					{
						if (!(pValue is Boolean))
						{
							RaiseParamTypeException(pParamName, pValue.GetType().Name, "Boolean");
						}
						break;
					}
				case SqlDbType.Text:
				case SqlDbType.NText:
				case SqlDbType.NChar:
				case SqlDbType.VarChar:
				case SqlDbType.NVarChar:
				case SqlDbType.Char:
				case SqlDbType.Xml:
					{
						if (!(pValue is String))
						{
							RaiseParamTypeException(pParamName, pValue.GetType().Name, "String");
						}

						break;
					}
				case SqlDbType.TinyInt:
					{
						if (!(pValue is Byte))
						{
							RaiseParamTypeException(pParamName, pValue.GetType().Name, "Byte");
						}
						break;
					}
				case SqlDbType.Variant:
					{
						break;
					}
				case SqlDbType.Float:
					{
						if (!(pValue is double))
						{
							RaiseParamTypeException(pParamName, pValue.GetType().Name, "double");
						}
						break;
					}
				case SqlDbType.BigInt:
					{
						if (!(pValue is Int64))
						{
							RaiseParamTypeException(pParamName, pValue.GetType().Name, "Int64");
						}
						break;
					}
				case SqlDbType.SmallInt:
					{
						if (!(pValue is Int16))
						{
							RaiseParamTypeException(pParamName, pValue.GetType().Name, "Int16");
						}
						break;
					}
				case SqlDbType.Int:
					{
						if (!(pValue is Int32))
						{
							RaiseParamTypeException(pParamName, pValue.GetType().Name, "Int32");
						}
						break;
					}
				case SqlDbType.Time:
					{
						if (!(pValue is TimeSpan))
						{
							RaiseParamTypeException(pParamName, pValue.GetType().Name, "TimeSpan");
						}
						break;
					}
				case SqlDbType.UniqueIdentifier:
					{
						if (!(pValue is Guid))
						{
							RaiseParamTypeException(pParamName, pValue.GetType().Name, "Guid");
						}
						break;
					}
				case SqlDbType.Decimal:
				case SqlDbType.SmallMoney:
				case SqlDbType.Money:
					{
						if (!(pValue is Decimal))
						{
							RaiseParamTypeException(pParamName, pValue.GetType().Name, "Decimal");
						}
						break;
					}
				case SqlDbType.Image:
				case SqlDbType.VarBinary:
				case SqlDbType.Timestamp:
				case SqlDbType.Binary:
					{
						if (!(pValue is byte[]))
						{
							RaiseParamTypeException(pParamName, pValue.GetType().Name, "Byte[]");
						}
						break;
					}
				case SqlDbType.Real:
					{
						if (!(pValue is Single))
						{
							RaiseParamTypeException(pParamName, pValue.GetType().Name, "Single");
						}
						break;
					}
				default:
					throw new ArgumentOutOfRangeException(pSqlType.ToString() + " is not a recognized type");
			}
		}

		/// <summary>
		/// Returns true if Size can be set on the given parameter type
		/// </summary>
		/// <param name="pSqlType"></param>
		/// <returns></returns>
		private static bool ParamIsLengthType(SqlDbType pSqlType)
		{
			switch (pSqlType)
			{
				case SqlDbType.Text:
				case SqlDbType.NText:
				case SqlDbType.NChar:
				case SqlDbType.VarChar:
				case SqlDbType.NVarChar:
				case SqlDbType.Char:
				case SqlDbType.Xml:
					return true;
				default:
					return false;
			}
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="sParamName"></param>
		/// <param name="sTypePassed"></param>
		/// <param name="sTypeExpected"></param>
		private static void RaiseParamTypeException(string sParamName, string sTypePassed, string sTypeExpected)
		{
			throw new ArgumentOutOfRangeException(sParamName, "Parameter '" + sParamName + "' is of type " + sTypePassed + " not " + sTypeExpected + ".");
		}

		/// -------------------------------------------------------------------
		/// <summary>
		///	Adds standard Return and Output params to the provided Cmd object
		/// </summary>
		/// -------------------------------------------------------------------
		public static void AddStandardReturnParams(System.Data.SqlClient.SqlCommand pCmd)
		{
			AddParamReturnValue(pCmd, SqlDbType.Int, "@RETURN_VALUE");
			AddParamOutput(pCmd, SqlDbType.VarChar, "@out_StatusMsg", 500);
		}		

		/// *********************************************************************************
		#endregion Parameters
		/// *********************************************************************************

		/// *********************************************************************************
		#region Execs
		/// *********************************************************************************

		/// -------------------------------------------------------------------
		/// <summary>
		/// Executes "NonQuery" (no result set) command already set up in the provided CMD object.
		/// </summary>
		/// <param name="cmd">Command Object</param>
		/// <param name="bCheckReturnValue">If true, an exception will be thrown if a non-zero value is returned from the proc.</param>
		/// <param name="sStatusMsg">The status message returned from the proc (if any)</param>
		/// <returns>boolean pass/fail</returns>
		/// -------------------------------------------------------------------
		public static bool ExecuteNonQuery(SqlCommand cmd, bool bCheckReturnValue, out string sStatusMsg)
		{
			//execute NonQuery and fetch return
			cmd.ExecuteNonQuery();

			//fetch status message and return value
			sStatusMsg = GetProcReturnMessage(cmd);

			//return Retval
			return CheckReturnValue(cmd, bCheckReturnValue);
		}

		/// -------------------------------------------------------------------
		/// <summary>
		///	Executes "NonQuery" (no result set) command already set up in the provided CMD object. Does not return Status Message from proc.
		/// </summary>
		/// <param name="cmd">Command Object</param>
		/// <param name="bCheckReturnValue">If true, an exception will be thrown if a non-zero value is returned from the proc.</param>
		/// <returns>	boolean pass/fail.</returns>
		/// -------------------------------------------------------------------
		public static bool ExecuteNonQuery(SqlCommand cmd, bool bCheckReturnValue)
		{
			string sDummyStatusMessage;
			return ExecuteNonQuery(cmd, bCheckReturnValue, out sDummyStatusMessage);
		}

		/// -------------------------------------------------------------------
		/// <summary>
		///	Executes ExecuteXmlReader and returns XML as string.
		/// </summary>
		/// <param name="cmd">Command Object</param>
		/// <param name="bCheckReturnValue">If true, an exception will be thrown if a non-zero value is returned from the proc.</param>
		/// <returns>string XML</returns>
		/// -------------------------------------------------------------------
		public static string ExecuteXMLReader(SqlCommand cmd, bool bCheckReturnValue)
		{
			//execute xmlreader
			XmlReader myXMLReader = cmd.ExecuteXmlReader();
			myXMLReader.MoveToContent();
			
			//fetch XML string
			string sXML = myXMLReader.ReadOuterXml();

			//check return value
			//todo: does the reader need to be closed first before fetching the return value?
			CheckReturnValue(cmd, bCheckReturnValue);

			//return XML as string
			return sXML;
		}
		
		/// -------------------------------------------------------------------
		/// <summary>
		///	Performs an ExecuteReader on the provided cmd object and Returns an OPEN sqldatareader (that must be closed later)
		/// </summary>
		/// <param name="cmd">Command Object</param>
		/// <returns>SqlDataReader</returns>
		/// -------------------------------------------------------------------
		public static SqlDataReader ExecOpenReader(SqlCommand cmd)
		{
			return cmd.ExecuteReader();
		}

		/// *********************************************************************************
		#endregion Execs
		/// *********************************************************************************

		/// *********************************************************************************
		#region Utility functions
		/// *********************************************************************************

		/// <summary>
		/// Called by DAL's to close the open reader given to it, and to fetch return values.
		/// </summary>
		/// <returns></returns>
		public static string CloseReaderAndGetReturnValues(SqlCommand cmd, SqlDataReader reader, bool bCheckReturnValue)
		{
			//reader must be closed to fetch return message
			if (reader != null)
			{
				reader.Close();
				CheckReturnValue(cmd, bCheckReturnValue);
				return GetProcReturnMessage(cmd);
			}

			return "";
		}

		/// <summary>
		/// Fetches the logged in username from the current httpcontext
		/// </summary>
		/// <returns></returns>
		public static string GetUserName()
		{
			string sUserName = "?";

			//fetch Identity username
			if (HttpContext.Current.User != null && HttpContext.Current.User.Identity.IsAuthenticated)
			{
				sUserName = HttpContext.Current.User.Identity.Name;
			}

			return sUserName;
		}

		/// -------------------------------------------------------------------
		/// <summary>
		/// Throws StoredProcReturnValue exception, with Proc Status Message included,
		/// if the @RETURN_VALUE is not included
		/// </summary>
		/// <param name="cmd">Command object</param>
		/// <param name="bCheckReturnValue">If the ReturnValue should be checked</param>
		/// <returns>@RETURN_VALUE, or throws exception</returns>
		/// -------------------------------------------------------------------
		public static bool CheckReturnValue(SqlCommand cmd, bool bCheckReturnValue)
		{
			//fetch return
			int iReturnValue = ((Int32)(cmd.Parameters["@RETURN_VALUE"].Value));

			//if not checking ret value, simply return the value
			if (!bCheckReturnValue)
			{
				return (iReturnValue == 0) ? true : false;
			}
			else
			{
				//raise exception if not 0
				if (iReturnValue != 0)
				{
					sdf.Code.Exceptions.StoredProcReturnValue(iReturnValue, cmd.CommandText.ToString(), GetProcReturnMessage(cmd));
				}

				//sucess. return value
				return (iReturnValue == 0)? true : false;
			}
		}

		/// -------------------------------------------------------------------
		/// <summary>
		/// Fetches the "@out_StatusMsg Return Message from the proc. Typically contains the row count.
		/// </summary>
		/// -------------------------------------------------------------------
		public static string GetProcReturnMessage(SqlCommand cmd)
		{
			string sStatusMsg = "";
			if (cmd.Parameters["@out_StatusMsg"].Value != System.DBNull.Value)
			{
				sStatusMsg = ((string)(cmd.Parameters["@out_StatusMsg"].Value));
			}

			return sStatusMsg;
		}

		/// *********************************************************************************
		#endregion Utility functions
		/// *********************************************************************************
	}
}